package com.gla.market.SearchHistoryDatabase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;

import com.gla.market.entity.MY_Type;
import com.gla.market.entity.SearchBean;

import java.util.ArrayList;
import java.util.List;

public class PetShopDatabaseHelper extends SQLiteOpenHelper {
    private static SQLiteDatabase db;
    public PetShopDatabaseHelper(@Nullable Context context) {
        super(context, "SearchHistory.db", null, 1);
        System.out.println("数据库创建成功");
        db = getReadableDatabase();
    }
    @Override
    public void onCreate(SQLiteDatabase db){
        String sql = ("CREATE TABLE history(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "userId VARCHAR(50) not null,"+
                "keyword VARCHAR(50) not null," +
                "time VARCHAR(50) not null)");
        db.execSQL(sql);

        String sql1 = ("CREATE TABLE typeNum(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "userId VARCHAR(50) not null,"+
                "type VARCHAR(50) not null,"+
                "num INTEGER)");
        db.execSQL(sql1);

    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists history");
        onCreate(db);
    }

    /**
     * 查询搜索历史是否已存在
     * ***/
    public static SearchBean QueryFromKeyword(String keyword,String userId){
        SearchBean searchBean = new SearchBean();
        String sql = "select * from history where keyword = '"+keyword+"' and userId = '"+userId+"'";
        Cursor cursor = db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            Integer id = cursor.getInt(cursor.getColumnIndex("id"));
            String userId1 = cursor.getString(cursor.getColumnIndex("userId"));
            String keyword1 = cursor.getString(cursor.getColumnIndex("keyword"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            searchBean = new SearchBean(id,userId1,keyword1,time);
        }
        return searchBean;
    }

    /***
     * 插入历史记录
     * **/
    public static void insert(String userId,String keyword,String date){
        db.execSQL("INSERT INTO history(userId,keyword,time)VALUES(?,?,?)",
                new String[]{userId,keyword,date});
    }

    /***
     * 修改历史记录的时间
     * **/
    public static void update(String userId,String keyword,String time){
       String sql = "update history set time = '"+time+"'where userId = '"+userId+"' and keyword = '"+keyword+"'";
        db.execSQL(sql);
    }

    /***
     *获取该用户的所有历史搜索记录
     * **/
    public static List<SearchBean> getUserHistory(String userId){
        List<SearchBean> lists = new ArrayList<>();
        String sql = "select * from history where userId = '"+userId+"'order by time Desc";
        Cursor cursor = db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            Integer id = cursor.getInt(cursor.getColumnIndex("id"));
            String userId1 = cursor.getString(cursor.getColumnIndex("userId"));
            String keyword1 = cursor.getString(cursor.getColumnIndex("keyword"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            SearchBean searchBean = new SearchBean(id,userId1,keyword1,time);
            lists.add(searchBean);
        }
        return lists;
    }
    /***
     * 删除该用户的所有历史记录
     * **/
    public static void deleteUserAll(String userId){
        String sql = "delete from history where userId = "+userId;
        db.execSQL(sql);
    }

    /***
     * 判断TYPE是否在用户点击类型表存在
     * **/
    public static boolean searchType(String type,String userId){
        String sql = "select * from typeNum where type = '"+type+"' and userId = '"+userId+"'";
        Cursor cursor = db.rawQuery(sql,null);
        if(cursor.moveToNext()){  //cursor没有数据返回false
            return true;
        }else{
            return false;
        }
    }

    /***
     * 插入用户的数据
     * **/
    public static void insertType(String type,String userId){
        db.execSQL("INSERT INTO typeNum(userId,type)VALUES(?,?)",
                new String[]{userId,type});
        String sql = "update typeNum set num = 1 where userId = '"+userId+"' and type = '"+type+"'";
        db.execSQL(sql);
    }

    /***
     * 添加该商品类别的数量
     * **/
    public static void updateType(String type,String userId){
        String sql = "update typeNum set num = num+1 where userId = '"+userId+"' and type = '"+type+"'";
        db.execSQL(sql);
    }

    /***
     * 获取该用户商品类别
     * **/
    public List<MY_Type> getUserType(String userId){
        List<MY_Type> lists = new ArrayList<>();
        String sql = "select * from typeNum where userId = '"+userId+"'";
        Cursor cursor = db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            Integer id = cursor.getInt(cursor.getColumnIndex("id"));
            String userId1 = cursor.getString(cursor.getColumnIndex("userId"));
            String type = cursor.getString(cursor.getColumnIndex("type"));
            Integer num = cursor.getInt(cursor.getColumnIndex("num"));
            MY_Type my_type = new MY_Type(id,userId1,type,num);
            lists.add(my_type);
        }
        return lists;
    }
}
